package homePage.dao;

import homePage.bean.Book;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class BookSearchDao {
    public List<Book> getByName(String bookName){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<Book> list = new ArrayList<>();
        try {
            //注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","liuzhenG2003");
            String sql = "SELECT * FROM book WHERE bookName= ?";
            statement = connection.prepareStatement(sql);
            statement.setString(1,bookName);

            resultSet = statement.executeQuery();
            Book book = new Book();
            while (resultSet.next()) {
                String author = resultSet.getString("author");
                String kind = resultSet.getString("kind");
                int currrent_number = resultSet.getInt("current_number");
                int all_number = resultSet.getInt("all_number");
                String position =resultSet.getString("position");
                String src = resultSet.getString("src");
                String recommend = resultSet.getString("recommend");
                book = new Book();
                book.setBookName(bookName);
                book.setAuthor(author);
                book.setKind(kind);
                book.setCurrrent_number(currrent_number);
                book.setAll_number(all_number);
                book.setPosition(position);
                book.setSrc(src);
                book.setRecommend(recommend);
                if (currrent_number==0){
                    book.setState("已借光");
                }else {
                    book.setState("借书");
                }
                list.add(book);
            }
            System.out.println(list);

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (statement!=null){
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }
    public List<Book> getByAuthor(String author){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<Book> list = new ArrayList<>();
        try {
            //注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","liuzhenG2003");
            String sql = "SELECT * FROM book WHERE author=?";
            statement = connection.prepareStatement(sql);
            statement.setString(1,author);
            resultSet = statement.executeQuery();
            Book book = null;
            while (resultSet.next()) {
                String bookName = resultSet.getString("bookName");
                String kind = resultSet.getString("kind");
                int currrent_number = resultSet.getInt("current_number");
                int all_number = resultSet.getInt("all_number");
                String position =resultSet.getString("position");
                String src = resultSet.getString("src");
                String recommend = resultSet.getString("recommend");

                book = new Book();
                book.setBookName(bookName);
                book.setAuthor(author);
                book.setKind(kind);
                book.setCurrrent_number(currrent_number);
                book.setAll_number(all_number);
                book.setPosition(position);
                book.setSrc(src);
                book.setRecommend(recommend);
                if (currrent_number==0){
                    book.setState("已借光");
                }else {
                    book.setState("借书");
                }
                list.add(book);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (statement!=null){
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }
    public List<Book> getByKind(String kind){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<Book> list = new ArrayList<>();
        try {
            //注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","liuzhenG2003");
            String sql = "SELECT * FROM book WHERE kind= ?";
            statement = connection.prepareStatement(sql);
            statement.setString(1,kind);
            resultSet = statement.executeQuery();
            Book book = null;
            while (resultSet.next()) {
                String bookName = resultSet.getString("bookName");
                String author = resultSet.getString("kind");
                int currrent_number = resultSet.getInt("current_number");
                int all_number = resultSet.getInt("all_number");
                String position =resultSet.getString("position");
                String src = resultSet.getString("src");
                String recommend = resultSet.getString("recommend");
                book = new Book();
                book.setBookName(bookName);
                book.setAuthor(author);
                book.setKind(kind);
                book.setCurrrent_number(currrent_number);
                book.setAll_number(all_number);
                book.setPosition(position);
                book.setSrc(src);
                book.setRecommend(recommend);
                if (currrent_number==0){
                    book.setState("已借光");
                }else {
                    book.setState("借书");
                }
                list.add(book);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (statement!=null){
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }
    public List<Book> getAllBook(){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<Book> list = new ArrayList<>();
        try {
            //注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","liuzhenG2003");
            String sql = "SELECT * FROM book ";
            statement = connection.prepareStatement(sql);

            resultSet = statement.executeQuery();
            Book book = new Book();
            while (resultSet.next()) {
                String author = resultSet.getString("author");
                String bookName = resultSet.getString("bookName");
                String kind = resultSet.getString("kind");
                int currrent_number = resultSet.getInt("current_number");
                int all_number = resultSet.getInt("all_number");
                String position =resultSet.getString("position");
                String src = resultSet.getString("src");
                String recommend = resultSet.getString("recommend");
                book = new Book();
                book.setBookName(bookName);
                book.setAuthor(author);
                book.setKind(kind);
                book.setCurrrent_number(currrent_number);
                book.setAll_number(all_number);
                book.setPosition(position);
                book.setSrc(src);
                book.setRecommend(recommend);
                if (currrent_number==0){
                    book.setState("已借光");
                }else {
                    book.setState("借书");
                }
                list.add(book);
            }
            System.out.println(list);

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (statement!=null){
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }

}
